import time
import pandas as pd
import pymysql

conn = None
cursor = None

def _sql_init():
    """
    初始化数据库连接

    -------
    Returns
    -------
    conn:pymysql.connection
        数据库连接对象
    """
    usr = "root"
    passwd = "hdjt2022DATA!"
    host = "192.168.111.205"
    # db = "tax_test"
    db = "tax"
    charset = "utf8"
    try:
        global conn, cursor
        if conn is None:
            conn = pymysql.connect(
                host=host,
                user=usr,
                password=passwd,
                database=db,
                charset=charset
            )
            # 创建游标对象
            cursor = conn.cursor()
    except BaseException:
        if cursor is not None:
            cursor.close()
        if conn is not None:
            conn.close()
        raise ValueError("连接异常！！！！！！！！！！！！！！！！！！！\n请联系数据人员！！！！！！！！！！！！！！！！！！！！")


def colse_commit():
    global conn, cursor
    conn.commit()
    cursor.close()
    conn.close()
    conn = None



def insert_data(df, table_name):
    """
    向指定表插入数据
    """
    sql = """
        INSERT INTO 
        {}( 
            id_card, 
            filename,
            dtime 
        ) 
        VALUES 
        (%s, %s, %s);
    """.format(table_name)  # 使用字符串格式化来插入表名

    for j in df.index:
        cursor.execute(sql, (
            df.loc[j, 'id_card'],
            df.loc[j, 'addr'],
            df.loc[j, 'dtime']
        ))

def main():
    df5 = pd.read_excel('2.xlsx', sheet_name=0)
    df4 = pd.read_excel('2.xlsx', sheet_name=1)
    df5['dtime'] = time.time()
    df4['dtime'] = time.time()
    # df = df4.copy()
    _sql_init()
    # 分别处理两个DataFrame
    insert_data(df5, "first_month_05")
    insert_data(df4, "first_month_04")
    conn.commit()
    colse_commit()
    print("完成")


if __name__ == '__main__':
    main()
